# -*- coding:utf-8 -*-

class PlaneManageSql(object):
    
    PLANE_ADD = "INSERT INTO plane( flag, " \
               "status, creater_id, start_time, end_time) " \
               "VALUES ( %s, %s, %s, %s, %s)"
    
    PLANE_DEL = "DELETE FROM plane WHERE id=%s"
    
    PLANE_EDIT = "UPDATE plane SET object_id=%s," \
                 "flag=%s, status=%s, start_time=%s, end_time=%s WHERE id=%s"
    
    PLANE_LIST = "SELECT u.user_name,p.id, p.flag, " \
                 "p.status, p.start_time, p.end_time FROM plane as p " \
                 "INNER JOIN user_info as u ON u.id=p.creater_id WHERE u.id=%s ORDER BY p.id desc "

    PLANE_INFO = "SELECT p.id, p.flag, " \
                 "p.status, p.start_time, p.end_time FROM plane as p " \
                 "INNER JOIN user_info as u ON u.id=p.creater_id WHERE p.id=%s"

    PLANE_LIST_ALL = "SELECT u.user_name,p.id, p.flag, " \
                    "p.status, p.start_time, p.end_time FROM plane as p " \
                    "INNER JOIN user_info as u ON u.id=p.creater_id WHERE (u.id=%s or %s>2) ORDER BY p.id desc "

    PLANE_LIST_IN_PROJECT = "select u.user_name,pl.* from user_info as u " \
                            "INNER JOIN group_project as gp ON u.id=gp.user_id " \
                            "INNER JOIN plane as pl ON pl.creater_id=u.id " \
                            "WHERE gp.object_id " \
                            "IN(select p.id from group_project as gp " \
                            "INNER JOIN project as p ON p.id=gp.object_id " \
                            "WHERE gp.user_id=%s) AND pl.start_time < (select current_date) AND pl.start_time > (select CURRENT_TIMESTAMP::TIMESTAMP - INTERVAL '7 day') ORDER BY pl.id DESC"